# -*- coding:utf-8 -*-
# !/usr/bin/env python
# 作者：士超
# 创建：2019-11-01
# 更新：2020-03-05
# 用意：链接MYSQL数据库，用于查询，统计，保存数据
import time
import pymysql
import gol
from const import const
import datetime


class _MySqlHelper(object):

    def __init__(self, dbname=None):
        rq = time.strftime('%Y%m%d', time.localtime(time.time()))
        siteData = const.SITEDATA + rq + '_SQL_Data.txt'
        self.siteData = open(siteData, 'a+', encoding='utf-8')
        self.count = 0
        self.con = None
        if dbname is not None:
            try:
                self.con = pymysql.connect(host=const.MYSQL_HOST,
                                           port=int(const.MYSQL_PORT),
                                           user=const.MYSQL_USER,
                                           passwd=const.MYSQL_PWD,
                                           db=dbname,
                                           charset=const.MYSQL_CHARSET)
            except Exception as e:
                print("Mysqldb Error:%s" % e)

    def get_connect(self):

        # 连接数据库
        try:
            self.con = pymysql.connect(host=const.MYSQL_HOST,
                                       port=int(const.MYSQL_PORT),
                                       user=const.MYSQL_USER,
                                       passwd=const.MYSQL_PWD,
                                       db=gol.get_value('dbName'),
                                       charset=const.MYSQL_CHARSET)
            return self.con
        except Exception as e:
            print("Mysqldb Error:%s" % e)

    def select(self, sql):
        try:
            con = self.get_connect()
            cur = con.cursor()
            count = cur.execute(sql)
            fc = cur.fetchall()
            cur.close()
            return fc
        except Exception as e:
            print("Mysqldb Error:%s" % e)
            print(sql)
        finally:
            con.close()

    def select_count(self, sql):
        try:
            con = self.get_connect()
            cur = con.cursor()
            count = cur.execute(sql)
            cur.close()
            return cur.fetchone()[0]
        except Exception as e:
            print("Mysqldb Error:%s" % e)
            print(sql)
        finally:
            con.close()

    def execute(self, sql, params=None, log=None):

        try:
            if sql == "":
                return
            if self.con is None:
                self.con = self.get_connect()
            self.con.ping(reconnect=True)
            if params:
                count = self.con.cursor().execute(sql, params)
            else:
                count = self.con.cursor().execute(sql)
            if self.count == 5000:
                print("上传一次")
                self.con.commit()
                self.count = 0

            return count
        except Exception as e:
            self.con.rollback()
            print("Mysqldb Error:%s" % e)
            print(sql)
            print(params)

    def executemany(self, sql, params, log=None):
        try:
            con = self.get_connect()
            cur = con.cursor()
            count = cur.executemany(sql, params)
            con.commit()
            cur.close()
            return count
        except Exception as e:
            con.rollback()
            print("Mysqldb Error:%s" % e)
            print(sql)
            print(params)
        finally:
            con.close()

    def executeAll(self, lists, log=None):
        try:
            con = self.get_connect()
            cur = con.cursor()
            for sql in lists:
                if isinstance(sql, tuple):
                    cur.execute(sql[0])
                else:
                    cur.execute(sql)
            con.commit()
            cur.close()
        except Exception as e:
            con.rollback()
            print("Mysqldb Error:%s" % e)
            print(sql)
        finally:
            con.close()

    def closeCon(self):
        print("最终上传")
        self.con.commit()
        self.con.close()

    def commit(self):
        if self.con is not None:
            self.con.ping(reconnect=True)
            self.con.commit()
